from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import or_,and_
from flask import Flask
import os
import sqlite3
import base64
import hashlib
app = Flask(__name__)
base_dir = os.path.abspath(os.path.dirname(__file__))
app.config['SQLALCHEMY_DATABASE_URI']='sqlite:///'+os.path.join(base_dir, r'test.sqlite')
#创建数据库操作对象
db = SQLAlchemy(app)
import time

#创建一个用户的模型
class User(db.Model):
    __tablename__='Users'
    uid = db.Column(db.Integer,primary_key=True)
    uname = db.Column(db.String(),unique=True)
    password = db.Column(db.String())
    liveaddress = db.Column(db.String())
    address = db.Column(db.String())
    career = db.Column(db.String())
    tel = db.Column(db.String())
    usermsg = db.Column(db.String())
    profileimg=db.Column(db.LargeBinary())
    #定义模型构造方法
    def __init__(self,uname,password,liveaddress,address,career,tel,usermsg,profileimg) -> None:
        # self.uid = uid
        self.uname = uname
        self.password = password
        self.liveaddress=liveaddress
        self.address=address
        self.career=career
        self.tel=tel
        self.usermsg=usermsg
        self.profileimg = profileimg
    def __repr__(self) -> str:
        return '{0},{1}'.format(self.uname,self.password)
#后台管理-用户分页查询的方法
def userpagequery(uname,liveaddress,career,address,tel,currpage = 1):
    #动态查询条件设置
    conditions = [] #存储查询条件
    if uname != '':
        conditions.append(User.uname.like("%"+uname+"%"))
    if liveaddress != '':
        conditions.append(User.liveaddress.like("%"+liveaddress+"%"))
    if career != '':
        conditions.append(User.career.like("%"+career+"%"))
    if address != '':
        conditions.append(User.address ==address) 
    if tel !='':
        conditions.append(User.tel == tel)           
    page = db.session.query(User).filter(*conditions).order_by(User.uid).paginate(page=currpage,per_page=5,error_out=False)
    return page

#后台管理-添加用户信息
def add_usermsg(umsg):
    try:
        db.session.add(umsg)
        db.session.commit()
        return '用户添加成功!'
    except:
        db.session.rollback()
        return '用户添加失败!可能为用户名重复'
    
#后台管理-删除用户信息
def del_usermsg(uid):
    db.session.query(User).filter_by(uid=uid).delete()
    db.session.commit()
    return '删除成功!'

#后台管理-更改用户信息
def update_user(getid,uname,password,liveaddress,address,career,tel,usermsg,profileimg):
    if profileimg == b'':
       update_dic ={'uname':uname,'password':password,'liveaddress':liveaddress,'address':address,'career':career,'tel':tel,'usermsg':usermsg}
    else:
       #print(profileimg)
       update_dic ={'uname':uname,'password':password,'liveaddress':liveaddress,'address':address,'career':career,'tel':tel,'usermsg':usermsg,'profileimg':profileimg}
    try:
       db.session.query(User).filter_by(uid=getid).update(update_dic)
       db.session.commit()
       return '用户信息修改成功!'
    except:
       return '用户信息修改失败!可能为用户名重复'

#用户头像图片的模型
class Userimg(db.Model):
    __table_args__ = {'extend_existing': True}
    __tablename__='Users'
    uid = db.Column(db.Integer,primary_key=True)
    uname = db.Column(db.String())
    profileimg=db.Column(db.LargeBinary())
    #定义模型构造方法
    def __init__(self,uid,uname,profileimg) -> None:
        self.uid = uid
        self.uname = uname
        self.profileimg = profileimg
class Userre(db.Model):
    __table_args__ = {'extend_existing': True}
    __tablename__='Users'
    uid = db.Column(db.Integer,primary_key=True)
    uname = db.Column(db.String())
    password=db.Column(db.String())
     #定义模型构造方法
    def __init__(self,uname,password) -> None:
        self.uname = uname
        self.password = password

def queryprofileimg(uname):
    imglist = db.session.query(Userimg).filter(Userimg.uname==uname).all()
    return imglist
def updateuserimg(getname,changeimg):
    conn = sqlite3.connect('test.sqlite')  
    cursor = conn.cursor()  
    try:
       cursor.execute("UPDATE users set profileimg = ? where uname = ? ",(changeimg,getname))  
       conn.commit()
    except:
        return "头像修改失败"
    finally:
        conn.close()
    return "头像修改成功"

#用户查询的方法
def loginguery(uname,password):
    user = db.session.query(User).filter(User.uname == uname,User.password == password).first()
    return user
def adduser(umsg):
    db.session.add(umsg)
    db.session.commit()
    return '用户注册成功!'
def queryusermsg(user):
    msglist = db.session.query(User).filter(User.uname==user).all()
    return msglist
    
#查看用户名是否唯一,返回一个列表,如果列表为空,则说明用户唯一
def queryunamelist(uname):
    unameres = db.session.query(User).filter(User.uname ==uname).all()
    return unameres
def queryallusers():
    user = db.session.query(User).all()
    #print(user)
    return user
def updateuser(getname,uname,address,liveaddress,career,tel,usermsg,password):
    if password == '':
       userdic = {'uname':uname,'address':address,'liveaddress':liveaddress,'career':career,'tel':tel,'usermsg':usermsg}
    else:
       password = hashlib.md5(password.encode()).hexdigest() 
       userdic={'uname':uname,'address':address,'liveaddress':liveaddress,'career':career,'tel':tel,'usermsg':usermsg,'password':password}
    db.session.query(User).filter_by(uname=getname).update(userdic)
    db.session.commit()
    db.session.rollback()
    return "修改成功"
 #调出人脸数据
def getface(uid):
    try:
       conn = sqlite3.connect('test.sqlite')  
       cursor = conn.cursor() 
       cursor.execute("SELECT profileimg FROM users WHERE uid=?",(str(uid)))  
       image_data_base64 = cursor.fetchone()[0] 
       return image_data_base64
    except:
        pass
    finally:
        conn.commit()
        conn.close()
def getuname():
    pass


#宠物信息部分飞非图片
class Petmsg(db.Model):
    __tablename__='pet_msg'
    #主键是int,默认自动增长
    pid = db.Column(db.Integer,primary_key=True)
    pname= db.Column(db.String(),unique=True)
    oname = db.Column(db.String())
    address=db.Column(db.String())
    ability=db.Column(db.String())
    pmsg=db.Column(db.String())
    pet_his=db.Column(db.String())
    pet_fit=db.Column(db.String())
    pet_clingy=db.Column(db.String())
    pet_kind=db.Column(db.String())
    pet_smart=db.Column(db.String())
    pet_honest=db.Column(db.String())
    bueaty=db.Column(db.String())
    pet_img=db.Column(db.String()) #原本为LargeBinary(),现在改为str
    pclass=db.Column(db.String(),db.ForeignKey("pclasstable.fk"))
    pclasstable= db.relationship("Pclasstable")
    #定义模型构造方法
    def __init__(self,pname,oname,address,ability,pmsg,pet_his,pet_fit,pet_clingy,pet_kind,pet_smart,pet_honest,bueaty,pet_img,pclass) -> None:
        # self.pid=pid
        self.pname = pname
        self.oname = oname
        self.address = address
        self.ability=ability
        self.pmsg = pmsg
        self.pet_his = pet_his
        self.pet_fit = pet_fit
        self.pet_clingy=pet_clingy
        self.pet_kind=pet_kind
        self.pet_smart=pet_smart
        self.pet_honest = pet_honest
        self.bueaty = bueaty
        self.pclass = pclass
        self.pet_img = pet_img
    def __repr__(self) -> str:
        return '({0},{1},{2},{3},{4},{5},{6},{7},{9},{10},{11},{12})'.format(self.pname,self.oname,self.address,self.ability,self.pmsg,self.pet_his,\
            self.pet_fit,self.pet_clingy,self.pet_kind,self.pet_smart,self.pet_honest,self.bueaty,self.pclass)
#这个是获取动物图片的类，以及pid号  
class Petimg(db.Model):
    __table_args__ = {'extend_existing': True}
    __tablename__='pet_msg'
    #主键是int,默认自动增长
    pid = db.Column(db.Integer,primary_key=True)
    pet_img=db.Column(db.LargeBinary())
    #定义模型构造方法
    def __init__(self,pid,pet_img):
      self.pid=pid
      self.pet_img=pet_img
    #这个是查询用的查询集
    def __repr__(self) -> str:
        return '({0},{1})'.format(self.pid,self.pet_img)
    
#查询所以宠物pid
def querypetid():
    plist=db.session.query(Petimg.pid).order_by(Petimg.pid).all()
    return plist
    
    
    
    
 #查询宠物信息非图片   
def petmsg_query():
     plist = db.session.query(Petmsg).all()
     return plist
#查询图片信息，返回一个存放二进制的列表集
def pet_img_query(pid):
    imglist=db.session.query(Petimg).filter(Petimg.pid==pid).all()
    return imglist

#添加宠物信息
def pet_add(pmsglist):
    try:
       db.session.add(pmsglist)
       db.session.commit()
       return "宠物信息添加成功!"
    except:
       db.session.rollback()
       return "宠物信息添加失败!可能为宠物名重复"


#删除宠物信息
def pet_del(pid):
    db.session.query(Petmsg).filter_by(pid=pid).delete()
    db.session.commit()
    return "删除成功!"

#修改宠物信息
def pet_update(getid,pname,oname,address,ability,pmsg,pet_his,pet_fit,pet_clingy,pet_kind,bueaty,pet_honest,pet_smart,pclass,pet_img):
    if pet_img == b'':
        udict={'pname':pname,'oname':oname,'address':address,'ability':ability,'pmsg':pmsg,'pet_his':pet_his,
        'pet_fit':pet_fit,'pet_clingy':pet_clingy,'pet_kind':pet_kind,'bueaty':bueaty,'pet_honest':pet_honest,
        'pet_smart':pet_smart,'pclass':pclass}
    else:
        udict={'pname':pname,'oname':oname,'address':address,'ability':ability,'pmsg':pmsg,'pet_his':pet_his,
        'pet_fit':pet_fit,'pet_clingy':pet_clingy,'pet_kind':pet_kind,'bueaty':bueaty,'pet_honest':pet_honest,
        'pet_smart':pet_smart,'pclass':pclass,'pet_img':pet_img}
    try:
        db.session.query(Petmsg).filter_by(pid=getid).update(udict)
        db.session.commit()
        return '宠物信息修改成功!'
    except:
        db.session.rollback()
        return '宠物信息修改失败!可能为宠物名重复'
#展示页分页查询
def querypage(pname,address,ability,pclass,currpage = 1):
    #动态查询条件设置
    conditions = [] #存储查询条件
    if pname != '':
        conditions.append(Petmsg.pname.like("%"+pname+"%"))
    if address != '':
        conditions.append(Petmsg.address.like("%"+address+"%"))
    if ability != '':
        conditions.append(Petmsg.ability.like("%"+ability+"%"))
    if pclass != '':
        conditions.append(Petmsg.pclass == pclass)            
    page = db.session.query(Petmsg).filter(*conditions).order_by(Petmsg.pid).paginate(page=currpage,per_page=2,error_out=False)
    return page

#后台分页查询
def admin_querypage(pname,address,ability,pclass,currpage = 1):
    #动态查询条件设置
    conditions = [] #存储查询条件
    if pname != '':
        conditions.append(Petmsg.pname.like("%"+pname+"%"))
    if address != '':
        conditions.append(Petmsg.address.like("%"+address+"%"))
    if ability != '':
        conditions.append(Petmsg.ability.like("%"+ability+"%"))
    if pclass != '':
        conditions.append(Petmsg.pclass == pclass)            
    page = db.session.query(Petmsg).filter(*conditions).order_by(Petmsg.pid).paginate(page=currpage,per_page=3,error_out=False)
    return page


#ai识别模型参数的结果
def querypname(pname):
    msglist = []
    plike = Petmsg.pname.like("%"+pname+"%")
    msglist.append(plike)
    res = db.session.query(Petmsg).filter(plike).all()
    #print(res)
    return res

class Pclasstable(db.Model):
    __tablename__='pclasstable'
    id =db.Column(db.Integer,primary_key=True)
    fk = db.Column(db.String(),unique=True)
    pclass= db.Column(db.String())
    petmsg = db.relationship("Petmsg")
    def __init__(self,fk,pclass):
      self.fk=fk
      self.pclass=pclass

def querypagepclass(cname,currpage=1):
    #动态查询条件设置
    conditions = [] #存储查询条件
    if cname != '':
        conditions.append(Pclasstable.fk.like("%"+cname+"%"))
    page = db.session.query(Pclasstable).filter(*conditions).order_by(Pclasstable.id).paginate(page=currpage,per_page=10,error_out=False)
    return page
#查询所有信息
def queryallpclass():
    classlist = db.session.query(Pclasstable).all()
    return classlist

#添加类别信息
def addclass(pclasslist):
    try:
        db.session.add(pclasslist)
        db.session.commit()
        return "宠物类别信息添加成功!"
    except:
        db.session.rollback()
        return "宠物类别信息添加失败!可能为类别名重复"
    
#删除类别信息
def delclass(delid):
    db.session.query(Pclasstable).filter_by(id=delid).delete()
    db.session.commit()
    return '删除成功!'
    
#编辑类别信息
def updateclass(getid,classname):
    classdict = {'fk':classname,'pclass':classname}
    try:
        db.session.query(Pclasstable).filter_by(id = getid).update(classdict)
        db.session.commit()
        return '宠物类别信息修改成功!'
    except:
        db.session.rollback()
        return '宠物类别信息修改失败!可能为类别名重复'
#用户说说部分
class Context(db.Model):
    __tablename__='context'
    cid = db.Column(db.Integer(),primary_key=True)
    uname= db.Column(db.String())
    context=db.Column(db.String())
    contime=db.Column(db.String())
    conimg =db.Column(db.String())
    def __init__(self,uname,context,contime,conimg):
      self.uname = uname
      self.context=context
      self.contime=contime
      self.conimg = conimg
#查所有说说信息
def querycontext():
    clist = db.session.query(Context).all()
    return clist
#查看个人说说
def queryselfcontext(uname):
    clist = db.session.query(Context).filter(Context.uname==uname).all()
    return clist   
#新增说说
def addcontext(uname,context,conimg):
    nowtime = time.strftime("%Y-%m-%d %H:%M:%S",time.localtime(time.time()))
    context = Context(uname=uname,context=context,contime=nowtime,conimg=conimg)
    db.session.add(context)
    db.session.commit()
    return "新加说说成功"
   
        
#删除说说
def delcontext(cid):
    db.session.query(Context).filter_by(cid=cid).delete()
    db.session.commit()
    return "删除成功"
#编辑说说
def editcontext(getid,context,filename):
    nowtime = time.strftime("%Y-%m-%d %H:%M:%S",time.localtime(time.time()))
    if filename != None:
        try:
            qur = db.session.query(Context).filter_by(cid=getid).all()
            #print(qur)
            os.remove('static\images\context\%s'%qur[0].conimg)
        except:
            print("没删掉")
        db.session.query(Context).filter_by(cid=getid).update({'context':context,"contime":nowtime,'conimg':filename}) 
        db.session.commit()  
        return "修改说说成功"
    else:
        db.session.query(Context).filter_by(cid=getid).update({'context':context,"contime":nowtime})
        db.session.commit() 
        return '修改说说成功'
    
class Comment(db.Model):
    __tablename__='comment'
    id = db.Column(db.Integer(),primary_key=True)
    cid = db.Column(db.Integer())
    comment=db.Column(db.String())
    commitname=db.Column(db.String())
    def __init__(self,cid,comment,commitname):
      self.cid = cid
      self.comment=comment
      self.commitname = commitname
def querycomment():
    colist = db.session.query(Comment).all()
    return colist
#按照条件查询评论
def querysomec(cid):
    somec = db.session.query(Comment).filter_by(cid = cid).all()
    return somec
#增加评论
def addcomment(cid,comment,commitname):
    commentlist = Comment(cid=cid,comment=comment,commitname=commitname)
    db.session.add(commentlist)
    db.session.commit()
#删除评论
def delcomment(id):
    db.session.query(Comment).filter_by(id=id).delete()
    db.session.commit()


    





